/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.database;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author HoangTien
 */
public class MyConnection {

    Connection conn;

    public MyConnection() {
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public boolean setupConnection() {
        String url = "jdbc:sqlserver://localhost:1433;databaseName=db_jsp";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(url, "sa", "123");
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }

    public ResultSet executeQuery(String spName) {
        try {
            if (this.setupConnection()) {
                CallableStatement cs = conn.prepareCall(spName);
                return cs.executeQuery();
            }
        } catch (SQLException e) {
            System.out.println(e);
        }

        return null;
    }

    public ResultSet executeQuery(String spName, List parameter) throws SQLException {
        if (this.setupConnection()) {
            String sqlUrl = "{call " + spName + "(";
            int index = 0;
            for (Object obj : parameter) {
                if (index != 0) {
                    sqlUrl += ",";
                }
                if (obj instanceof Integer) {
                    Integer i = (Integer) obj;
                    sqlUrl += i.toString();
                } else if (obj instanceof String) {
                    String s = (String) obj;
                    sqlUrl += "'" + s + "'";
                }
                index++;
            }
            sqlUrl += ")}";
            CallableStatement cs = conn.prepareCall(sqlUrl);
            return cs.executeQuery();
        }
        return null;
    }

    public void CloseAll(ResultSet rs) throws SQLException {
        rs.close();
        conn.close();
    }

    public int executeUpdate(String spName, List parameter) throws SQLException {
        if (this.setupConnection()) {
            String sqlUrl = "{call " + spName + "("; 
            int index = 0;
            for (Object obj : parameter) {
                if (index != 0) {
                    sqlUrl += ",";
                }
                if (obj instanceof Integer) {
                    Integer i = (Integer) obj;
                    sqlUrl += i.toString();
                } else if (obj instanceof Double) {
                    Double d = (Double) obj;
                    sqlUrl += d.toString();
                } else if (obj instanceof String) {
                    String s = (String) obj;
                    sqlUrl += "N'" + s + "'";
                } else if (obj instanceof Date) {
                    String s = String.valueOf(obj);
                    sqlUrl += "'" + s + "'";
                } else if (obj instanceof Boolean) {
                    boolean s = (Boolean) obj;
                    sqlUrl += "'" + s + "'";
                } else if (obj instanceof Float) {
                    Float f = (Float) obj;
                    sqlUrl += f.toString();
                }
                index++;
            }
            sqlUrl += ")}"; 
            CallableStatement cst = conn.prepareCall(sqlUrl);
            return cst.executeUpdate();
        }
        return -1;
    }

    @Override
    protected void finalize() throws Throwable {
        super.finalize();
        if(conn != null) {
            conn.close();
        } 
    } 
}
